-- Buat database
CREATE DATABASE smansab_lms;
USE smansab_lms;

-- Tabel lms_users
CREATE TABLE lms_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'guru', 'siswa') NOT NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    bio TEXT AFTER email,
    phone VARCHAR(25);
    foto_profil VARCHAR(255) DEFAULT NULL,
    tanggal_daftar TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive') DEFAULT 'active'
);

-- Tabel lms_categories
CREATE TABLE lms_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama_kategori VARCHAR(100) NOT NULL,
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel lms_courses
CREATE TABLE lms_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    judul VARCHAR(255) NOT NULL,
    deskripsi TEXT,
    id_guru INT NOT NULL,
    id_kategori INT NOT NULL,
    harga DECIMAL(10,2) DEFAULT 0.00,
    gambar_course VARCHAR(255) DEFAULT NULL,
    durasi_jam INT DEFAULT 0,
    level ENUM('pemula', 'menengah', 'lanjutan') DEFAULT 'pemula',
    rating DECIMAL(3,2) DEFAULT 0.00,
    total_siswa INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_guru) REFERENCES lms_users(id),
    FOREIGN KEY (id_kategori) REFERENCES lms_categories(id)
);

-- Tabel lms_enrollments
CREATE TABLE lms_enrollments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_siswa INT NOT NULL,
    id_course INT NOT NULL,
    progress INT DEFAULT 0,
    tanggal_enroll TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    tanggal_selesai TIMESTAMP NULL,
    status ENUM('active', 'completed', 'dropped') DEFAULT 'active',
    FOREIGN KEY (id_siswa) REFERENCES lms_users(id),
    FOREIGN KEY (id_course) REFERENCES lms_courses(id)
);

-- Tabel lms_lessons
CREATE TABLE lms_lessons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_course INT NOT NULL,
    judul_lesson VARCHAR(255) NOT NULL,
    konten TEXT,
    video_url VARCHAR(500) DEFAULT NULL,
    tipe_lesson ENUM('teks', 'video', 'quiz', 'tugas') DEFAULT 'teks',
    urutan INT NOT NULL,
    durasi_menit INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_course) REFERENCES lms_courses(id)
);

-- Tabel lms_quizzes
CREATE TABLE lms_quizzes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_lesson INT NOT NULL,
    judul_quiz VARCHAR(255) NOT NULL,
    deskripsi TEXT,
    waktu_menit INT DEFAULT 30,
    jumlah_soal INT DEFAULT 0,
    passing_grade INT DEFAULT 60,
    acak_soal TINYINT(1) DEFAULT 1,
    tampilkan_nilai TINYINT(1) DEFAULT 1,
    batas_percobaan INT DEFAULT 1;
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_lesson) REFERENCES lms_lessons(id)
);

-- Tabel lms_questions
CREATE TABLE lms_questions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_quiz INT NOT NULL,
    pertanyaan TEXT NOT NULL,
    tipe ENUM('pilihan_ganda', 'essay') DEFAULT 'pilihan_ganda',
    opsi_a VARCHAR(255),
    opsi_b VARCHAR(255),
    opsi_c VARCHAR(255),
    opsi_d VARCHAR(255),
    jawaban_benar CHAR(1),
    poin INT DEFAULT 1,
    FOREIGN KEY (id_quiz) REFERENCES lms_quizzes(id)
);

-- Tabel lms_quiz_attempts
CREATE TABLE lms_quiz_attempts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_siswa INT NOT NULL,
    id_quiz INT NOT NULL,
    nilai DECIMAL(5,2),
    waktu_mulai TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    waktu_selesai TIMESTAMP NULL,
    status ENUM('in_progress', 'completed') DEFAULT 'in_progress',
    FOREIGN KEY (id_siswa) REFERENCES lms_users(id),
    FOREIGN KEY (id_quiz) REFERENCES lms_quizzes(id)
);

-- Data dummy
INSERT INTO lms_users (username, password, role, nama_lengkap, email) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'Administrator', 'admin@smansab.id'),
('guru1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'guru', 'Budi Santoso, S.Kom', 'budi@smansab.id'),
('guru2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'guru', 'Sari Dewi, M.Pd', 'sari@smansab.id'),
('siswa1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'siswa', 'Andi Wijaya', 'andi@smansab.id'),
('siswa2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'siswa', 'Rina Melati', 'rina@smansab.id');

INSERT INTO lms_categories (nama_kategori, deskripsi) VALUES
('Web Development', 'Kursus pengembangan website dan aplikasi web'),
('Seni & Desain', 'Kursus seni, desain grafis dan kreativitas'),
('Programming', 'Kursus pemrograman dan algoritma'),
('Matematika', 'Kursus matematika dan logika'),
('Bahasa', 'Kursus bahasa Indonesia dan asing');

INSERT INTO lms_courses (judul, deskripsi, id_guru, id_kategori, harga, durasi_jam, level, rating, total_siswa) VALUES
('Web Coding dan Apache Basics', 'Belajar dasar-dasar pemrograman web dan konfigurasi server Apache', 2, 1, 0.00, 10, 'pemula', 4.5, 34),
('Real Things Art Painting', 'Kursus melukis dengan teknik dan bahan nyata', 3, 2, 0.00, 8, 'pemula', 5.0, 309),
('Engine Creating untuk Xbox One', 'Pengembangan game engine untuk platform Xbox', 2, 3, 0.00, 34, 'lanjutan', 5.0, 238),
('Console Development dengan Unity', 'Dasar-dasar pengembangan game console menggunakan Unity', 2, 3, 0.00, 34, 'menengah', 4.5, 238);